from flask import Blueprint, render_template, jsonify, request
from python.extensions import db
from python.model.weather import WeatherData
from sqlalchemy import func, extract, text, select
from sqlalchemy.exc import SQLAlchemyError
import datetime
import traceback

# 创建温度对比分析蓝图，URL前缀为/menu
temp_comparison_bp = Blueprint('temp_comparison', __name__, url_prefix='/menu')


@temp_comparison_bp.route('/temperature_comparison', methods=['GET'])
def temperature_comparison():
    """渲染城市月度温度对比页面"""
    try:
        # 获取所有城市
        cities = db.session.query(WeatherData.city).distinct().all()
        cities = [city[0].strip() for city in cities]

        # 获取所有年份
        years = db.session.query(func.year(WeatherData.date)).distinct().all()
        years = [str(year[0]) for year in years]
        years.sort(reverse=True)

        return render_template(
            'menu/radar.html',
            cities=cities,
            years=years
        )
    except SQLAlchemyError as e:
        db.session.rollback()
        print(f"渲染页面时数据库查询出错: {e}")
        return jsonify({"error": "获取城市或年份数据失败，请稍后重试"}), 500
    except Exception as e:
        print(f"渲染页面时出错: {e}")
        return jsonify({"error": "页面渲染失败，请稍后重试"}), 500


@temp_comparison_bp.route('/api/cities', methods=['GET'])
def get_cities():
    """获取所有城市列表"""
    try:
        cities = db.session.query(WeatherData.city).distinct().all()
        cities = [city[0].strip() for city in cities if city[0].strip()]
        return jsonify({
            "cities": cities
        })
    except SQLAlchemyError as e:
        db.session.rollback()
        print(f"获取城市列表时出错: {e}")
        return jsonify({"error": "获取城市数据失败，请稍后重试"}), 500
    except Exception as e:
        print(f"获取城市列表时出错: {e}")
        return jsonify({"error": "获取城市数据失败，请稍后重试"}), 500


@temp_comparison_bp.route('/api/years', methods=['GET'])
def get_years():
    """获取所有有数据的年份"""
    try:
        years = db.session.query(func.year(WeatherData.date)).distinct().all()
        years = [str(year[0]) for year in years]
        years.sort(reverse=True)
        return jsonify({
            "years": years
        })
    except SQLAlchemyError as e:
        db.session.rollback()
        print(f"获取年份列表时出错: {e}")
        return jsonify({"error": "获取年份数据失败，请稍后重试"}), 500
    except Exception as e:
        print(f"获取年份列表时出错: {e}")
        return jsonify({"error": "获取年份数据失败，请稍后重试"}), 500


@temp_comparison_bp.route('/monthly_high_temperature', methods=['GET'])
def monthly_high_temperature():
    """获取城市月度最高温度数据"""
    urban = request.args.get('urban', '').strip()

    if not urban:
        return jsonify({"error": "城市参数不能为空"}), 400

    try:
        # 使用 select().cte() 替代直接构造 CTE
        select_stmt = select(
            WeatherData.city,
            func.year(WeatherData.date).label("year"),
            func.month(WeatherData.date).label("month"),
            func.max(WeatherData.max_temp).label("max_temp")
        ).filter(
            WeatherData.city == urban
        ).group_by(
            WeatherData.city,
            func.year(WeatherData.date),
            func.month(WeatherData.date)
        )

        # 创建 CTE
        grouped_data = select_stmt.cte("grouped_data")

        # 在CTE基础上查询并格式化日期
        query = select(
            func.concat(grouped_data.c.year, "-", func.lpad(grouped_data.c.month, 2, "0")).label("month"),
            grouped_data.c.max_temp
        ).select_from(grouped_data).order_by(grouped_data.c.year, grouped_data.c.month)

        results = list(db.session.execute(query).all())

        if not results:
            return jsonify({
                "months": [],
                "high_temps": [],
                "message": f"未找到 {urban} 的最高温度数据",
            }), 200

        months = [result.month for result in results]
        high_temps = []
        for result in results:
            try:
                temp = float(result.max_temp)
                high_temps.append(round(temp, 1))
            except (ValueError, TypeError):
                high_temps.append(None)

        return jsonify({
            "months": months,
            "high_temps": high_temps
        }), 200

    except SQLAlchemyError as e:
        db.session.rollback()
        # 打印带实际参数的SQL语句
        sql = query.statement.compile(
            dialect=db.engine.dialect,
            compile_kwargs={"literal_binds": True}
        )
        print(f"获取最高温度数据时出错: {e}")
        print(f"执行的SQL: {sql}")
        return jsonify({"error": "获取最高温度数据失败，请稍后重试"}), 500
    except Exception as e:
        print(f"获取最高温度数据时出错: {e}")
        print(f"堆栈跟踪: {traceback.format_exc()}")
        return jsonify({"error": "获取最高温度数据失败，请稍后重试"}), 500


@temp_comparison_bp.route('/monthly_low_temperature', methods=['GET'])
def monthly_low_temperature():
    """获取城市月度最低温度数据"""
    urban = request.args.get('urban', '').strip()

    if not urban:
        return jsonify({"error": "城市参数不能为空"}), 400

    try:
        # 使用 select().cte() 替代直接构造 CTE
        select_stmt = select(
            WeatherData.city,
            func.year(WeatherData.date).label("year"),
            func.month(WeatherData.date).label("month"),
            func.min(WeatherData.min_temp).label("min_temp")
        ).filter(
            WeatherData.city == urban
        ).group_by(
            WeatherData.city,
            func.year(WeatherData.date),
            func.month(WeatherData.date)
        )

        # 创建 CTE
        grouped_data = select_stmt.cte("grouped_data")

        # 在CTE基础上查询并格式化日期
        query = select(
            func.concat(grouped_data.c.year, "-", func.lpad(grouped_data.c.month, 2, "0")).label("month"),
            grouped_data.c.min_temp
        ).select_from(grouped_data).order_by(grouped_data.c.year, grouped_data.c.month)

        results = list(db.session.execute(query).all())

        if not results:
            return jsonify({
                "months": [],
                "low_temps": [],
                "message": f"未找到 {urban} 的最低温度数据",
            }), 200

        months = [result.month for result in results]
        low_temps = []
        for result in results:
            try:
                temp = float(result.min_temp)
                low_temps.append(round(temp, 1))
            except (ValueError, TypeError):
                low_temps.append(None)

        return jsonify({
            "months": months,
            "low_temps": low_temps
        }), 200

    except SQLAlchemyError as e:
        db.session.rollback()
        # 打印带实际参数的SQL语句
        sql = query.statement.compile(
            dialect=db.engine.dialect,
            compile_kwargs={"literal_binds": True}
        )
        print(f"获取最低温度数据时出错: {e}")
        print(f"执行的SQL: {sql}")
        return jsonify({"error": "获取最低温度数据失败，请稍后重试"}), 500
    except Exception as e:
        print(f"获取最低温度数据时出错: {e}")
        print(f"堆栈跟踪: {traceback.format_exc()}")
        return jsonify({"error": "获取最低温度数据失败，请稍后重试"}), 500